SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>Database testing</title>

</head>

<span id="openzim-page-title" class="mw-page-title-main"><span class="mw-page-title-main">Database testing</span></span>

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .ambox{border:1px solid #a2a9b1;border-left:10px solid #36c;background-color:#fbfbfb;box-sizing:border-box}.mw-parser-output .ambox+link+.ambox,.mw-parser-output .ambox+link+style+.ambox,.mw-parser-output .ambox+link+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+style+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+link+.ambox{margin-top:-1px}html body.mediawiki .mw-parser-output .ambox.mbox-small-left{margin:4px 1em 4px 0;overflow:hidden;width:238px;border-collapse:collapse;font-size:88%;line-height:1.25em}.mw-parser-output .ambox-speedy{border-left:10px solid #b32424;background-color:#fee7e6}.mw-parser-output .ambox-delete{border-left:10px solid #b32424}.mw-parser-output .ambox-content{border-left:10px solid #f28500}.mw-parser-output .ambox-style{border-left:10px solid #fc3}.mw-parser-output .ambox-move{border-left:10px solid #9932cc}.mw-parser-output .ambox-protection{border-left:10px solid #a2a9b1}.mw-parser-output .ambox .mbox-text{border:none;padding:0.25em 0.5em;width:100%}.mw-parser-output .ambox .mbox-image{border:none;padding:2px 0 2px 0.5em;text-align:center}.mw-parser-output .ambox .mbox-imageright{border:none;padding:2px 0.5em 2px 0;text-align:center}.mw-parser-output .ambox .mbox-empty-cell{border:none;padding:0;width:1px}.mw-parser-output .ambox .mbox-image-div{width:52px}@media(min-width:720px){.mw-parser-output .ambox{margin:0 10%}}@media print{body.ns-0 .mw-parser-output .ambox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1248332772">

/* start https://en.wikipedia.org/ */

.mw-parser-output .multiple-issues-text{width:95%;margin:0.2em 0}.mw-parser-output .multiple-issues-text>.mw-collapsible-content{margin-top:0.3em}.mw-parser-output .compact-ambox .ambox{border:none;border-collapse:collapse;background-color:transparent;margin:0 0 0 1.6em!important;padding:0!important;width:auto;display:block}body.mediawiki .mw-parser-output .compact-ambox .ambox.mbox-small-left{font-size:100%;width:auto;margin:0}.mw-parser-output .compact-ambox .ambox .mbox-text{padding:0!important;margin:0!important}.mw-parser-output .compact-ambox .ambox .mbox-text-span{display:list-item;line-height:1.5em;list-style-type:disc}body.skin-minerva .mw-parser-output .multiple-issues-text>.mw-collapsible-toggle,.mw-parser-output .compact-ambox .ambox .mbox-image,.mw-parser-output .compact-ambox .ambox .mbox-imageright,.mw-parser-output .compact-ambox .ambox .mbox-empty-cell,.mw-parser-output .compact-ambox .hide-when-compact{display:none}

/* end https://en.wikipedia.org/ */

</style>

<p><b>Database testing</b> usually consists of a layered process, including the <a href="User_interface" title="User interface">user interface</a> (UI) layer, the business layer, the data access layer and the database itself. The UI layer deals with the interface design of the database, while the business layer includes databases supporting <a href="Strategic_management" title="Strategic management">business strategies</a>.

</p>

<div class="mw-heading mw-heading2"><h2 id="Purposes">Purposes</h2></div>

<p><a href="Database" title="Database">Databases</a>, the collection of interconnected files on a server, storing information, may not deal with the same <i>type</i> of data, i.e. databases may be <a href="Homogeneity_and_heterogeneity" title="Homogeneity and heterogeneity">heterogeneous</a>. As a result, many kinds of implementation and integration <a href="Software_bug" title="Software bug">errors</a> may occur in large database systems, which negatively affect the system's performance, reliability, consistency and security. Thus, it is important to <b><a href="Software_testing" title="Software testing">test</a></b> in order to obtain a database system which satisfies the <a href="ACID" title="ACID">ACID</a> properties (Atomicity, Consistency, Isolation, and Durability) of a <a href="Database_management_system" class="mw-redirect" title="Database management system">database management system</a>.<sup id="cite_ref-Database_1-0" class="reference"><a href="#cite_note-Database-1"><span class="cite-bracket">[</span>1<span class="cite-bracket">]</span></a></sup>

</p><p>One of the most critical layers is the data access layer, which deals with databases directly during the communication process. Database testing mainly takes place at this layer and involves testing strategies such as quality control and quality assurance of the product databases.<sup id="cite_ref-2" class="reference"><a href="#cite_note-2"><span class="cite-bracket">[</span>2<span class="cite-bracket">]</span></a></sup> Testing at these different layers is frequently used to maintain the consistency of database systems, most commonly seen in the following examples:

</p>

<ul><li>Data is critical from a business point of view. Companies such as <a href="Google" title="Google">Google</a> or <a href="NortonLifeLock" class="mw-redirect" title="NortonLifeLock">Symantec</a>, who are associated with <a href="Computer_data_storage" title="Computer data storage">data storage</a>, need to have a durable and consistent database system. If database operations such as <a href="SQL" title="SQL">insert, delete, and update</a> are performed without testing the database for consistency first, the company risks a crash of the entire system.</li>

<li>Some companies have different types of databases, and also different goals and missions. In order to achieve a level of functionality to meet said goals, they need to test their database system.</li>

<li>There may need to be more than the current approach of testing in which developers formally test the databases. However, this approach is not sufficiently effective since database developers are likely to slow down the testing process due to communication gaps. A separate database testing team seems advisable.</li>

<li>Database testing mainly deals with finding errors in the databases so as to eliminate them. This will improve the quality of the database or web-based system.</li>

<li>Database testing should be distinguished from strategies to deal with other problems such as database crashes, broken insertions, deletions or updates. Here, <a href="Database_refactoring" title="Database refactoring">database refactoring</a> is an evolutionary technique that may apply.</li></ul>

<div class="mw-heading mw-heading2"><h2 id="Types_of_testings_and_processes">Types of testings and processes</h2></div>

<p>The figure indicates the areas of testing involved during different database testing methods, such as <a href="Black-box_testing" title="Black-box testing">black-box testing</a> and <a href="White-box_testing" title="White-box testing">white-box testing</a>.

</p>

<div class="mw-heading mw-heading3"><h3 id="Black-box">Black-box</h3></div>

<p>Black-box testing involves testing interfaces and the integration of the database, which includes:

</p>

<ol><li>Mapping of data (including <a href="Metadata" title="Metadata">metadata</a>)</li>

<li>Verifying incoming data</li>

<li>Verifying outgoing data from query functions</li>

<li>Various techniques such as Cause effect graphing technique, <a href="Equivalence_partitioning" title="Equivalence partitioning">equivalence partitioning</a> and <a href="Boundary-value_analysis" title="Boundary-value analysis">boundary-value analysis</a>.</li></ol>

<p>With the help of these techniques, the functionality of the database can be tested thoroughly.

</p><p>Pros and Cons of black box testing include: Test case generation in black box testing is fairly simple. Their generation is completely independent of software development and can be done in an early stage of development. As a consequence, the programmer has better knowledge of how to design the database application and uses less time for debugging. Cost for development of black box test cases is lower than development of white box test cases. The major drawback of black box testing is that it is unknown how much of the program is being tested. Also, certain errors cannot be detected.<sup id="cite_ref-3" class="reference"><a href="#cite_note-3"><span class="cite-bracket">[</span>3<span class="cite-bracket">]</span></a></sup>

</p>

<div class="mw-heading mw-heading3"><h3 id="White-box">White-box</h3></div>

<p>White-box testing mainly deals with the internal structure of the database. The specification details are hidden from the user.

</p>

<ol><li>It involves the testing of database triggers and logical views which are going to support <a href="Database_refactoring" title="Database refactoring">database refactoring</a>.</li>

<li>It performs module testing of database functions, triggers, views, <a href="SQL" title="SQL">SQL</a> queries etc.</li>

<li>It validates database tables, data models, database schema etc.</li>

<li>It checks rules of <a href="Referential_integrity" title="Referential integrity">Referential integrity</a>.</li>

<li>It selects default table values to check on database consistency.</li>

<li>The techniques used in white box testing are condition coverage, decision coverage, statement coverage, <a href="Cyclomatic_complexity" title="Cyclomatic complexity">cyclomatic complexity</a>.</li></ol>

<p>The main advantage of white box testing in database testing is that coding errors are detected, so internal bugs in the database can be eliminated. The limitation of white box testing is that SQL statements are not covered.

</p>

<div class="mw-heading mw-heading3"><h3 id="The_WHODATE_approach">The WHODATE approach</h3></div>

<p>While generating test cases for database testing, the semantics of SQL statement need to be reflected in the test cases. For that purpose, a technique called WHite bOx Database Application Technique "(WHODATE)" is used. As shown in the figure, SQL statements are independently converted into GPL statements, followed by traditional white box testing to generate test cases which include SQL semantics.<sup id="cite_ref-4" class="reference"><a href="#cite_note-4"><span class="cite-bracket">[</span>4<span class="cite-bracket">]</span></a></sup>

</p>

<div class="mw-heading mw-heading2"><h2 id="Four_stages">Four stages</h2></div>

<ul><li>Set <a href="Test_fixture" title="Test fixture">fixture</a></li>

<li>Outcome verification</li>

<p>A set fixture describes the initial state of the database before entering the testing. After setting fixtures, database behavior is tested for defined test cases. Depending on the outcome, test cases are either modified or kept as is. The "tear down" stage either results in terminating testing or continuing with other test cases.<sup id="cite_ref-5" class="reference"><a href="#cite_note-5"><span class="cite-bracket">[</span>5<span class="cite-bracket">]</span></a></sup>

</p><p>For successful database testing the following workflow executed by each single test is commonly executed:

</p>

<ol><li>Clean up the database: If the testable data is already present in the database, the database needs to be emptied.</li>

<li>Set up fixture: A tool like <a href="PHPUnit" title="PHPUnit">PHPUnit</a> will then iterate over fixtures and do insertions into the database.</li>

<li>Run test, Verify outcome and then Tear down: After resetting the database to empty and listing the fixtures, the test is run and the output is verified. If the output is as expected, the tear down process follows, otherwise testing is repeated.</li></ol>

<div class="mw-heading mw-heading2"><h2 id="Basic_techniques">Basic techniques</h2></div>

<ul><li>SQL Query Analyzer is a helpful tool when using <a href="Microsoft_SQL_Server" title="Microsoft SQL Server">Microsoft SQL Server</a>.</li>

<li>One commonly used function, <code>create_input_dialog["label"]</code>, is used to validate the output with user inputs.</li>

<li>The design of forms for automated database testing, form front-end and back-end, is helpful to database maintenance workers.</li>

<li>Data <a href="Load_testing" title="Load testing">load testing</a>:

<ul><li>For data load testing, knowledge about source database and destination database is required.</li>

<li>Workers check the compatibility between source database and destination database using the <a href="Data_Transformation_Services" title="Data Transformation Services">DTS</a> package.</li>

<li>When updating the source database, workers make sure to compare it with the target database.</li>

<li>Database load testing measures the capacity of the database server to handle queries as well as the response time of database server and client.<sup id="cite_ref-6" class="reference"><a href="#cite_note-6"><span class="cite-bracket">[</span>6<span class="cite-bracket">]</span></a></sup></li></ul></li>

<li>In database testing, issues such as atomicity, consistency, isolation, durability, integrity, execution of triggers, and recovery are often considered.</li></ul>

<ol><li>The setup for database testing is costly and complex to maintain because database systems are constantly changing with expected insert, delete and update operations.</li>

<li>Extra overhead is involved in order to determine the state of the database transactions.</li>

<li>After cleaning up the database, new test cases have to be designed.</li>

<li>An SQL generator is needed to transform SQL statements in order to include the SQL semantic into database test cases.</li></ol>

<p><br>

</p>

<ul><li><a href="Database_normalization" title="Database normalization">Database normalization</a></li>

<li><a href="Software_testing" title="Software testing">Software testing</a></li>

<li><a href="Unit_testing" title="Unit testing">Unit testing</a></li></ul>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("./mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("./mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("./mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("./mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite id="CITEREFKorth2010" class="citation book cs1"><a href="Henry_F._Korth" title="Henry F. Korth">Korth, Henry</a> (2010). <i>Database System Concepts</i>. Macgraw-Hill. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-0-07-352332-3</bdi>.</cite></span>

</li>

<li id="cite_note-2"><span class="mw-cite-backlink"><b><a href="#cite_ref-2">^</a></b></span> <span class="reference-text"><cite id="CITEREFAmbler2003" class="citation book cs1">Ambler, Scott (2003). <i>Agile database Techniques: effective strategies for the agile software developer</i>. wiley. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-0-471-20283-7</bdi>.</cite></span>

</li>

<li id="cite_note-3"><span class="mw-cite-backlink"><b><a href="#cite_ref-3">^</a></b></span> <span class="reference-text"><cite id="CITEREFPressman1994" class="citation book cs1">Pressman, Roger (1994). <i>Software Tester: A Practitioner's Approach</i>. McGraw-Hill Education. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-0-07-707732-7</bdi>.</cite></span>

</li>

<li id="cite_note-4"><span class="mw-cite-backlink"><b><a href="#cite_ref-4">^</a></b></span> <span class="reference-text"><cite id="CITEREFZhang1999" class="citation book cs1">Zhang, Yanchun (1999). <i>Cooperative databases and applications '99: the proceedings of the Second International Symposium on Cooperative Database Systems for Advanced Applications (CODAS '99), Wollongong, Australia, March 27–28, 1999</i>. Springer. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-981-4021-64-7</bdi>.</cite></span>

</li>

<li id="cite_note-5"><span class="mw-cite-backlink"><b><a href="#cite_ref-5">^</a></b></span> <span class="reference-text"><cite id="CITEREFKan" class="citation book cs1">Kan, Stephen. <i>Metrics & Models in Software Quality Engineering</i>. Pearson Education. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-81-297-0175-6</bdi>.</cite></span>

</li>

<li id="cite_note-6"><span class="mw-cite-backlink"><b><a href="#cite_ref-6">^</a></b></span> <span class="reference-text"><cite class="citation news cs1"><a rel="nofollow" class="external text" href="https://books.google.com/books?id=zj4EAAAAMBAJ&q=database+load+testing">"InfoWorld"</a>. <i>InfoWorld Media Group, Inc</i>. 15 Jan 1996.</cite></span>

</li>

</ol></div></div>

<ul><li><cite id="CITEREFAmbler,_Scott_W.2006" class="citation web cs1">Ambler, Scott W. (2006). <a rel="nofollow" class="external text" href="http://www.agiledata.org/essays/databaseTesting.html">"Database Testing: How to Regression Test a Relational Database"</a>. <i>Agile Data</i><span class="reference-accessdate">. Retrieved <span class="nowrap">December 4,</span> 2011</span>.</cite></li>

<li><cite id="CITEREFZeichick,_Alan1989" class="citation book cs1">Zeichick, Alan; et al. (August 14, 1989). <a rel="nofollow" class="external text" href="https://books.google.com/books?id=sjAEAAAAMBAJ&q=Database+Testing&pg=PA46"><i>How We Tested Integrated Software Packages</i></a>. InfoWorld<span class="reference-accessdate">. Retrieved <span class="nowrap">December 4,</span> 2011</span>.</cite></li></ul>

<div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2></div>

<ul><li><a rel="nofollow" class="external text" href="https://web.archive.org/web/20130518084433/http://phpunit.de/manual/3.8/en/database.html">Chapter 8. Database Testing</a>, from PHPUnit Manual</li>

<li><a rel="nofollow" class="external text" href="https://www.amazon.co.uk/Creating-Datasets-Testing-Relational-Databases/dp/3847337637">Creating Datasets for Testing Relational Databases</a></li>

<li><a rel="nofollow" class="external text" href="https://web.archive.org/web/20190129064117/http://test4data.com/sqlcoverage.aspx?lang=en">SQL Test Coverage</a></li>

<li><a rel="nofollow" class="external text" href="http://acolyte.eu.org">Acolyte Framework</a> to mock up JDBC for persistence testing</li></ul></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2023-08-10" href="https://en.wikipedia.org/wiki/?title=Database_testing&oldid=1169704991">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>